# encoding=utf-8
import json
import sqlite3, os
import time

from flask import g


FORMAT = '%Y-%m-%d %X'

BASE_DIR = os.path.dirname(__file__)
DATABASE = os.path.join(BASE_DIR, 'data/server.db')

def connect_db():
    return sqlite3.connect(DATABASE)
def get_db():
    db = getattr(g, '_user', None)
    if db is None:
        db = g._user = connect_db()
    return db

def register_ip(ip, port):
    try:
        conn = connect_db()
        
        sql = "insert into iplist(ip,port) values(?,?)"
        conn.execute(sql, (ip, port))
        conn.commit()    
        conn.close()
        return True
    except:
        return False
# {'ip','value',....}    

def save_report(args):
    try:       
        t = time.strftime(FORMAT, time.localtime())
        conn = connect_db()
        conn.text_factory = str
        sql = "insert into report(ip,port,server_name,result,release_time) values(?,?,?,?,?)"
        conn.execute(sql, (args["ip"], args["port"], args["server_name"], args["result"], t))
        conn.commit()
        conn.close()
        return True
    except Exception, e:
        print e
        return False

def remove():
    try:
        conn = connect_db()
        sql = "delete from iplist"
        conn.execute(sql)
        conn.commit()    
        conn.close()
        return True
    except:
        return False
def update():
    try:
        conn = connect_db()
        sql = "delete from sqlite_sequence"
        conn.execute(sql)
        conn.commit()    
        conn.close()
        return True
    except:
        return False

def query_ip():
    list = []
    conn = connect_db()
    sql = "select * from iplist"
    cursor = conn.execute(sql)
   
    for row in cursor:
        list.append(row)        
    return list
            
def query_report(ip, port, servname):
    results = []
    conn = connect_db()
    conn.text_factory = str
    sql = "select * from report where ip=? and port=? and server_name=? order by release_time desc "
    cursor = conn.execute(sql, (ip, port, servname))
    
    for row in cursor:
        return row[4]
               

    
              
# register_ip('192.168.1.111',5000)  
# print query_ip()

# print query_report('192.168.1.243', 8000, 'zjjc')
